package cjgl.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import cjgl.entity.Student;

public class StudentDAO {
	public Student findById(Integer id) {
		Student stu = null;
		String sql="select * from student t where t.id="+id;
		Connection conn = ConnectionFactory.getConnection();
		if(conn!=null) {
		Statement stmt;
		try {
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			if(rs.next()) {
				stu = new Student(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4),rs.getInt(5));
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		}
		return stu;
	}
	
	
	public List<Student> findAll(){
		List<Student> list = new ArrayList<Student>();
		String sql = "select * from student";
		Connection conn = ConnectionFactory.getConnection();
		if(conn!=null) {
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while(rs.next()) {
				Student bj=new Student(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4),rs.getInt(5));
				list.add(bj);
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		}
		return list;
		
	}
	
	
	public synchronized void insert(Student student) {
		String sql="insert into student values(null,?,?,?,?)";
		Connection conn = ConnectionFactory.getConnection();
		if(conn!=null) {
		PreparedStatement pstmt;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, student.getName());
			pstmt.setString(2, student.getPassword());
			pstmt.setString(3, student.getStuNo());
			pstmt.setInt(4, student.getTbclass().getId());
			pstmt.execute();
			sql="select * from student t order by t.id desc";
			ResultSet rs = pstmt.executeQuery(sql);
			rs.next();
			student.setId(rs.getInt(1));
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		}
	}
	
	public void update(Student student) {
		String sql = "update student t set t.name=?,t.password=?,t.stuNo=?,t.tbClassId=? where t.id=?";
		Connection conn = ConnectionFactory.getConnection();
		if(conn!=null) {
			PreparedStatement pstmt;
			try {
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, student.getName());
				pstmt.setString(2, student.getPassword());
				pstmt.setString(3, student.getStuNo());
				pstmt.setInt(4,student.getTbclass().getId());
				pstmt.setInt(5,student.getId());
				pstmt.execute();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
		}
	}
	
	
	public void delete(Integer id) {
		String sql="delete from student Where id="+id;
		Connection conn = ConnectionFactory.getConnection();
		if(conn!=null) {
			Statement stmt;
			try {
				stmt = conn.createStatement();
				stmt.execute(sql);
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	
	
	public Student findByUsernameAndPassword(String username, String password) {
		Student stu = null;
		String sql ="select * from student where stuNo=? and password=?";
		Connection conn = ConnectionFactory.getConnection();
		if(conn!=null) {
		PreparedStatement pstmt;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, username);
			pstmt.setString(2, password);
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()) {
				stu = new Student(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getInt(5));
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		}
		return stu;
	}


	public List<Student> findByTbClassId(Integer id) {
		List<Student> list = new ArrayList<Student>();
		String sql = "select * from student where tbClassId=?";
		Connection conn = ConnectionFactory.getConnection();
		if(conn!=null) {
			PreparedStatement pstmt;
			try {
				pstmt = conn.prepareStatement(sql);
				pstmt.setInt(1,id);
				ResultSet rs = pstmt.executeQuery();
				while(rs.next()) {
					Student stu = new Student(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4),rs.getInt(5));
					list.add(stu);
				}
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	
}



